package com.apobates.jforum.grief.schema2doc.jdbc.dialect;

import com.apobates.jforum.grief.schema2doc.core.SchemaTableStrategy;
import com.apobates.jforum.grief.schema2doc.core.entity.Column;
import com.apobates.jforum.grief.schema2doc.core.entity.Nullable;
import com.apobates.jforum.grief.schema2doc.core.entity.PrimayKey;
import com.apobates.jforum.grief.schema2doc.core.entity.Table;
import com.apobates.jforum.grief.schema2doc.jdbc.schema.AbstractQueryExecutor;
import com.apobates.jforum.grief.schema2doc.core.schema.SchemaDialect;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.sql.DataSource;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
import java.util.stream.Stream;
/**
 * SQLServer的结果集供应商
 */
public class JooqQuerySQLServerExecutor extends AbstractQueryExecutor {
    private final static Logger logger = LoggerFactory.getLogger(JooqQuerySQLServerExecutor.class);

    public JooqQuerySQLServerExecutor(DataSource dataSource, SchemaDialect dialect) {
        super(dataSource, dialect);
    }

    @Override
    protected Stream<Table> getTables(DSLContext context, Optional<String> dbName, Optional<String> schema, SchemaTableStrategy tableStrategy) throws IllegalArgumentException {
        // select * from ESB_pub_client.INFORMATION_SCHEMA.TABLES
        /*
        SELECT a.name AS tableName, CONVERT(NVARCHAR(100),isnull(g.[value],'-')) AS remarks
            FROM sys.tables a LEFT JOIN sys.extended_properties g ON (a.object_id = g.major_id AND g.minor_id = 0)
            WHERE CHARINDEX('JB_', a.name) = 1;
        * SELECT a.name AS tableName, CONVERT(NVARCHAR(100),isnull(g.[value],'-')) AS remarks FROM sys.tables a LEFT JOIN sys.extended_properties g ON (a.object_id = g.major_id AND g.minor_id = 0)
        * */
        String sql = "SELECT a.object_id, a.name AS tableName, CONVERT(NVARCHAR(100),isnull(g.[value],'-')) AS remarks FROM sys.tables a LEFT JOIN sys.extended_properties g ON (a.object_id = g.major_id AND g.minor_id = 0)";
        if(tableStrategy.sql(SchemaDialect.SQLServer).isPresent()){
            sql+= " WHERE " + tableStrategy.sql(SchemaDialect.SQLServer).get().replaceAll(SchemaTableStrategy.placeholder, "a.name");
        }
        logger.debug("[TABLE-SQLServer]execute sql statement::"+sql);
        return context.fetch(sql).stream().map(record -> {
            // String dbName, String tableName, String remarks
            return Table.noSchema(
                    record.get("object_id", int.class),
                    dbName.get(),
                    record.get("tableName", String.class),
                    record.get("remarks", String.class));
        }).filter(table->tableStrategy.filter().test(table.getTableName()));
    }

    @Override
    protected Stream<Column> getColumns(DSLContext context, Table table) {
        /* @basic: String columnName, String typeName, String columnSize, Nullable nullable, String remarks
        * SELECT
COLUMN_NAME AS columnName,
IS_NULLABLE as nullable,
DATA_TYPE as typeName,
NUMERIC_SCALE AS decimalDigits,
COLUMN_DEFAULT AS columnDef,
CHARACTER_OCTET_LENGTH as columnSize,
CHARACTER_MAXIMUM_LENGTH
FROM ESB_pub_client.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'JB_CLML';
-------------------------------------------------

SELECT B.name AS columnName, B.max_length as columnSize, B.scale AS decimalDigits, B.precision, B.is_nullable AS nullable, T.name AS typeName, C.value AS remarks, D.definition AS columnDef
　　FROM sys.tables A　INNER JOIN sys.columns B ON B.object_id = A.object_id INNER JOIN sys.types T ON B.system_type_id = T.system_type_id LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id LEFT JOIN sys.default_constraints D ON B.object_id = D.parent_object_id AND B.column_id = D.parent_column_id
　　WHERE A.name = :tablename
        * */
        final List<String> pks = getPrimayKey(context, table.getTableName());
        String sql = "SELECT B.name AS columnName, B.max_length as columnSize, B.scale AS decimalDigits, B.precision, B.is_nullable AS nullable, T.name AS typeName, C.value AS remarks, D.definition AS columnDef " +
                "FROM sys.tables A　" +
                "INNER JOIN sys.columns B ON B.object_id = A.object_id " +
                "INNER JOIN sys.types T ON B.system_type_id = T.system_type_id " +
                "LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id " +
                "LEFT JOIN sys.default_constraints D ON B.object_id = D.parent_object_id AND B.column_id = D.parent_column_id " +
                "WHERE A.name = :tablename";
        // Fetch results using jOOQ
        return context.fetch(sql, DSL.param("tablename", table.getTableName())).stream().map(record -> {
            // Basic:: String columnName, String typeName, String columnSize, Nullable nullable, String remarks
            String columnName = record.get("columnName", String.class);
            return Column.basic(
                            columnName,
                            record.get("typeName", String.class),
                            record.get("columnSize", String.class),
                            Nullable.of(record.get("nullable", Integer.class)),
                            record.get("remarks", String.class))
                    .toFull(
                            record.get("decimalDigits", String.class),
                            pks.contains(columnName)?PrimayKey.YES:PrimayKey.NO,
                            record.get("columnDef", String.class)); // String decimalDigits, PrimayKey pk, String columnDef
        });
    }
    private List<String> getPrimayKey(DSLContext context, String tableName){
        // SELECT COLUMN_NAME  FROM  ESB_pub_client.INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE  TABLE_NAME= 'JB_CLML'
        String sql = "SELECT COLUMN_NAME AS columnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = :TN ";
        return context.fetch(sql, DSL.param("TN", tableName)).stream().map(record -> {
            return record.get("columnName", String.class);
        }).collect(Collectors.toList());

    }
}
